Case (5) Analysis of specific financial data
Project I.
Case details
Mr. Wang, a senior engineer in a high-tech company, recently plans to purchase a home in Guangzhou with a total price of RMB 10 million. He is unable to pay in full due to his own limited funds and intends to apply for a home mortgage loan from the local Bank C. Assuming that you are the account manager of Bank C responsible for expanding the housing mortgage loan business, after assessing Mr. Wang's repayment ability, you formulate the following loan proposal: the principal amount of the loan is RMB 6 million, the loan term is 30 years, and the interest rate of the loan is 5 basis points above the market quoted rate (LPR) for loans of 5 years or more, i.e. the loan interest rate is 4.9%. However, for this loan there are two repayment options available to Mr. Wong as follows. (1) Equal principal and interest repayment, which specifically means that the sum of the monthly repayment of principal and interest for Mr. Wong as a borrower remains the same while the interest rate level of the loan remains unchanged. (2) Equal principal repayment, which specifically means that Mr. Wang's monthly principal repayment is fixed and the interest paid decreases each month while the interest rate level of the loan remains the same. To give Mr. Wang a clear understanding of the differences between these two repayment methods and to demonstrate the loan repayments with the help of a graph, you need to complete 3 programming tasks using Python.
Programming tasks
(1) Assuming that you have chosen equal principal and interest repayment, calculate the amount that Mr. Wang needs to repay each month, as well as the principal and interest amounts of the monthly repayment, and visualise the relevant data. (2) In order to show Mr Wong the effect of a change in the interest rate on the monthly repayment amount under the equal principal rule, you use the following sensitivity analysis: that is, you model and visualise the change in Mr Wong's monthly repayment amount when the interest rate on the loan increases from 2%/year to 8%/year. (3) Assuming that the equal principal repayment rule is applied and the loan interest rate remains at 4.9%/year, calculate the principal and interest components of Mr. Wong's monthly repayments separately and visualise the results.
Start programming.
# -*- coding: utf-8 -*-
"""
Created on Tue Sept 22 8:47:37 2020
@author: mly
"""
import numpy as np
import matplotlib.pyplot as plt
# (1)
dp_rate = 0.049 # Loan Rates
loan_pv = 6000000 # Principal amount of loan Unit: $
loan_year = 30 # Loan term (years)
repay_mon = -round(np.pmt(dp_rate / 12, loan_year * 12, loan_pv)) # Monthly Repayment Amount
interestList = [] # List of interest per instalment
capitalList = [] # List of principal repayments required per instalment
monthList = [x for x in range(loan_year * 12)] # List of repayment periods
rest = loan_pv #Defining the remaining principal repayment in lieu
for i in range(loan_year * 12):
interest = round(rest * (dp_rate / 12)) # Interest per instalment
interestList.append(interest) # Insert the interest for each period into the list of interest
repay_capital = repay_mon - interest # Principal amount to be repaid per instalment
capitalList.append(repay_capital) # Insert the principal amount to be repaid for each period into the list of principal amounts to be repaid
rest = round(rest - repay_capital) # Principal remaining to be repaid
# Plotting stacked bar charts
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False #Show Chinese
plt.bar(monthList, capitalList, align="center", color="#EE9A49", label='每月本金额')
plt.bar(monthList, interestList, align="center", bottom=capitalList, color="#000000", label='每月利息额')
plt.xlabel('还款期限（月）')
plt.ylabel('每月还款额（元）')
plt.title('等额本息还款图')
plt.legend()
plt.show()
#(2)
# Sensitivity analysis of 2% to 8% p.a.
rates = [x / 100 for x in range(2, 9, 1)] # Loan APR
repaymentNL = []
for n in range(len(rates)):
repaymentN = -round(np.pmt(rates[n] / 12, loan_year * 12, loan_pv)) # Monthly Repayment Amount
repaymentNL.append(repaymentN)
plt.plot(rates, repaymentNL, lw=6,color="#EE9A49", label="每月还款额")
plt.fill_between(rates, 0, repaymentNL, facecolor="#000000", alpha=1)
plt.xlabel('年利率')
plt.ylabel('每月还款额（元）')
plt.title('年利率在2%~8%变化时每月还款额变化趋势')
plt.legend()
plt.show()
# Equal principal
repay_capitalX = loan_pv / (loan_year * 12) # Principal amount of each repayment in equal instalments
repay_capitalXL = [repay_capitalX for i in range(loan_year * 12)]
restX = loan_pv # Initial principal to be repaid
interestXList = [] # List of interest per instalment
repaymentXL = [] # List of principal repayments required per instalment
for i in range(loan_year * 12):
interestX = round(restX * (dp_rate / 12)) # Interest per instalment
interestXList.append(interestX) # Insert the interest for each period into the list of interest
restX = round(restX - repay_capitalX) # Principal remaining to be repaid
repaymentX = interestX + repay_capitalX # Repayment amount per instalment
repaymentXL.append(repaymentX)
# Plotting stacked bar charts
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
plt.plot(monthList, repaymentXL)
plt.bar(monthList, repay_capitalXL, align="center", color="#000000", label='每期本金额')
plt.bar(monthList, interestXList, align="center", bottom=repay_capitalXL, color="#EE9A49", label='每期利息额')
plt.xlabel('还款期限（月）')
plt.ylabel('每期还款额（元）')
plt.title('等额本金还款图')
plt.legend()
plt.show()
Presentation of results
